alter table [Contract]
add PackageId int null
go
ALTER TABLE [dbo].[Contract]  WITH CHECK ADD  CONSTRAINT [FK_Contract_TravelPackage] FOREIGN KEY([PackageId])
REFERENCES [dbo].[TravelPackage] ([PackageId])
GO
ALTER TABLE [dbo].[Contract] CHECK CONSTRAINT [FK_Contract_TravelPackage]
GO
ALTER PROCEDURE [dbo].[spContractEdit]
(
	@ContractId int,
	@ContactId int,
	@TemplateId int,
	@Status varchar(50),
	@AgentId int,
	@PackageId int,
	
	@CardNumber varchar(50) = null,
	@CardDate varchar(20) = null,
	@CardCVV varchar(10) = null,
	@CardCC varchar(10) = null,
	
	@TemplateFieldId varchar(max),
	@ContactColumnName varchar(max),
	@ContactColumnValue varchar(max)
)
AS
BEGIN
	set nocount on
	set xact_abort on
		
    begin transaction
		if (@ContractId = -1)
		begin
			insert into [Contract]
			(ContactId, TemplateId, [Status], AgentId, CardNumber, CardDate, CardCVV, CardCC, PackageId)
			values (@ContactId, @TemplateId, @Status, @AgentId, @CardNumber, @CardDate, @CardCVV, @CardCC, @PackageId)
			
			set @ContractId = SCOPE_IDENTITY()
		end
		else begin
			update [Contract]
			set TemplateId = @TemplateId, PackageId = @PackageId,
				CardNumber = @CardNumber, CardDate = @CardDate, CardCVV = @CardCVV, CardCC = @CardCC
			where (ContractId = @ContractId)
		end
		
		delete 
		from ContractField
		where (ContractId = @ContractId)
		
		insert into ContractField
		(ContractId, TemplateFieldId, ContactColumnName, ContactColumnValue)
		select @ContractId, vwTemplate.Item, vwColumnName.Item, vwColumnValue.Item 
		from dbo.fnStringSplit(@TemplateFieldId, ';') as vwTemplate inner join dbo.fnStringSplit(@ContactColumnName, ';') as vwColumnName on vwTemplate.IdItem = vwColumnName.IdItem
																	inner join dbo.fnStringSplit(@ContactColumnValue, ';') as vwColumnValue on vwTemplate.IdItem = vwColumnValue.IdItem

	commit transaction
	
	select @ContractId as ContractId
END
go
ALTER PROCEDURE [dbo].[spContract_SetStatus]
(
	@ContractId int,
	@Status varchar(50),
	
	@ContractLinkValidDays int = null,
	@IP varchar(20) = null
)
AS
BEGIN
	set nocount on
	set xact_abort on
	
	if (@Status = 'Sent')
	begin
		declare @LinkCode varchar(255), @ExpiredLinkDate datetime
		set @LinkCode = CONVERT(varchar(255), NEWID())
		set @ExpiredLinkDate = DATEADD(day, @ContractLinkValidDays, convert(date, GETDATE()))
		
		-- if first code generation
		if exists(select 1 from [Contract] where (ContractId = @ContractId) and (LinkCode is null))
			update [Contract]
			set [Status] = @Status, ContractDate = CONVERT(date, getdate()),
				LinkCode = @LinkCode, ExpiredLinkDate = @ExpiredLinkDate
			where ContractId = @ContractId
		else
			update [Contract]
			set LinkCode = @LinkCode, ExpiredLinkDate = @ExpiredLinkDate
			where ContractId = @ContractId
	end
	
	if (@Status = 'Viewed')
	begin
		if exists(select 1 from [Contract] where (ContractId = @ContractId) and (ViewDate is null))
			update [Contract]
			set [Status] = @Status,
				ViewDate = GETDATE(), ViewIP = @IP
			where ContractId = @ContractId
	end
	
	if (@Status = 'Signed')
	begin
		if exists(select 1 from [Contract] where (ContractId = @ContractId) and (SignedDate is null))
		begin
			declare @PackageId int, @ContactId int
			select @PackageId = PackageId, @ContactId = ContactId
			from [Contract] (nolock)
			where ContractId = @ContractId
			
			begin transaction
				update [Contract]
				set [Status] = @Status,
					SignedDate = GETDATE(), SignedIP = @IP
				where ContractId = @ContractId
				
				if (@PackageId is not null)
					insert into ClientPackage
					(ClientId, [Status], StartDate, ExpDate, PackageId)
					values(@ContactId, 'NEW', GETDATE(), GETDATE(), @PackageId)
			commit transaction
		end
	end
END
go